﻿
CREATE PROCEDURE [dbo].[proc_ContactPerson_Create]
	(
		@CustomerId uniqueidentifier,
		@ZName nvarchar(50),
		@Sex nvarchar(10),
		@Position nvarchar(20),
		@OfficeTel nvarchar(50),
		@HomeTel nvarchar(50),
		@Mobile nvarchar(50),
		@Email nvarchar(50),
		@IsMain bit,
		@PersonTypeId int,
		@PersonAge int,
		@Weixin nvarchar(50)
	)
AS
Begin
	If @IsMain=1
		Update ContactPerson Set IsMain=0 Where CustomerId=@CustomerId
	if (@PersonAge=0) Set @PersonAge=null;

	Insert Into ContactPerson(CustomerId,ZName,Sex,[Position],OfficeTel,HomeTel,Mobile,Email,IsMain,PersonTypeId,Age,Weixin) Values
	(@CustomerId,@ZName,@Sex,@Position,@OfficeTel,@HomeTel,@Mobile,@Email,@IsMain,@PersonTypeId,@PersonAge,@Weixin)

	Insert Into ContactPerson_Log(CustomerId,ZName,Sex,[Position],OfficeTel,HomeTel,Mobile,Email,IsMain,PersonTypeId,Age,Weixin,CreateDate) Values
	(@CustomerId,@ZName,@Sex,@Position,@OfficeTel,@HomeTel,@Mobile,@Email,@IsMain,@PersonTypeId,@PersonAge,@Weixin,GETDATE())

--重新获取联系人和电话
	Declare @p_name nvarchar(50),@p_tel varchar(50)

	Select Top 1 @p_name=ZName,@p_tel=Tel From(
		Select Mobile As Tel,ZName,IsMain,PersonTypeId,'A' as sort
		From ContactPerson
		Where CustomerId=@CustomerId
		And Mobile<>'' And (Mobile Is Not Null)
		Union All
		Select OfficeTel As Tel,ZName,IsMain,PersonTypeId,'B' as sort
		From ContactPerson
		Where CustomerId=@CustomerId
		And OfficeTel<>'' And (OfficeTel Is Not Null)
		Union All
		Select HomeTel As Tel,ZName,IsMain,PersonTypeId,'C' as sort
		From ContactPerson
		Where CustomerId=@CustomerId
		And HomeTel<>'' And (HomeTel Is Not Null)
	) a Order By IsMain Desc,PersonTypeId,sort

--更新客户及成单
	If @IsMain=1
	Begin
		Update [Customer] Set ZName=@p_name,Tel=@p_tel Where CustomerId=@CustomerId
		Update [Order] Set CusPerson=@p_name,CusPersonTel=@p_tel Where CusId=@CustomerId
	End
End